{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 读取数据\n",
    "data = pd.read_excel('./result/result2_1.xlsx')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 任务 3 肥料产品的多维度对比分析"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 任务 3.1 从文件“result2_1.xlsx”中提取发证日期中的年份，分析比较复混肥料中各组别不同年份产品登记数量的变化趋势。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>数量</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>发证年份</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2012</th>\n",
       "      <td>47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013</th>\n",
       "      <td>643</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014</th>\n",
       "      <td>823</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2015</th>\n",
       "      <td>1185</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016</th>\n",
       "      <td>1329</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017</th>\n",
       "      <td>974</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018</th>\n",
       "      <td>387</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019</th>\n",
       "      <td>324</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020</th>\n",
       "      <td>241</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        数量\n",
       "发证年份      \n",
       "2012    47\n",
       "2013   643\n",
       "2014   823\n",
       "2015  1185\n",
       "2016  1329\n",
       "2017   974\n",
       "2018   387\n",
       "2019   324\n",
       "2020   241"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 筛选出 复混肥料\n",
    "data = data[data['产品通用名称']=='复混肥料']\n",
    "\n",
    "# 将发证日期改为时间类型\n",
    "data['发证日期'] = pd.to_datetime(data['发证日期'])\n",
    "\n",
    "# 提取出发证年份\n",
    "data['发证年份'] = data['发证日期'].dt.year\n",
    "\n",
    "# 将数据根据发证年份进行分组得到每个年份的数量\n",
    "counts = data.groupby('发证年份').aggregate(\"count\")\n",
    "counts = counts[['group']]\n",
    "\n",
    "# 修改列名\n",
    "counts.rename(columns={'group':'数量'},inplace=True) \n",
    "\n",
    "# 导出文件\n",
    "counts.to_csv('./result/year_counts.csv',encoding='gbk')\n",
    "counts"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![](https://i.bmp.ovh/imgs/2021/11/12d6292c5fa6c7da.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 任务 3.2 从文件“result2_2.xlsx”中提取 2021 年 9 月 30 日仍有效的有机肥料产品,将完整的结果保存到文件“result3_2.xlsx”中。从有效产品中分别筛选出广西和湖北（根据正式登记证号区分）产品登记数量在前 5 的组别，分析两个省份上述组别的分布差异。请在报告中给出处理过程及分析结果。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Unnamed: 0</th>\n",
       "      <th>序号</th>\n",
       "      <th>企业名称</th>\n",
       "      <th>产品通用名称</th>\n",
       "      <th>产品形态</th>\n",
       "      <th>总氮百分比</th>\n",
       "      <th>P2O5百分比</th>\n",
       "      <th>K2O百分比</th>\n",
       "      <th>含氯情况</th>\n",
       "      <th>有机质百分比</th>\n",
       "      <th>正式登记证号</th>\n",
       "      <th>发证日期</th>\n",
       "      <th>有效期</th>\n",
       "      <th>产品商品名称</th>\n",
       "      <th>适用作物</th>\n",
       "      <th>总无机养分百分比</th>\n",
       "      <th>label</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>779</td>\n",
       "      <td>780</td>\n",
       "      <td>恒发新天地生物科技(大悟)有限公司</td>\n",
       "      <td>有机肥料</td>\n",
       "      <td>粉状</td>\n",
       "      <td>0.0200</td>\n",
       "      <td>0.0200</td>\n",
       "      <td>0.0200</td>\n",
       "      <td>无氯</td>\n",
       "      <td>0.45</td>\n",
       "      <td>鄂农肥（2011）准字0873号</td>\n",
       "      <td>2016-11-18</td>\n",
       "      <td>2021-11-01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0600</td>\n",
       "      <td>6,1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>110</th>\n",
       "      <td>3655</td>\n",
       "      <td>3656</td>\n",
       "      <td>湖北三宁化工股份有限公司</td>\n",
       "      <td>有机肥料</td>\n",
       "      <td>粒状</td>\n",
       "      <td>0.0167</td>\n",
       "      <td>0.0167</td>\n",
       "      <td>0.0167</td>\n",
       "      <td>无氯</td>\n",
       "      <td>0.45</td>\n",
       "      <td>鄂农肥（2016）准字2047号</td>\n",
       "      <td>2016-10-10</td>\n",
       "      <td>2021-10-01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0501</td>\n",
       "      <td>6,1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>111</th>\n",
       "      <td>3656</td>\n",
       "      <td>3657</td>\n",
       "      <td>荆州市丰禾源生物科技有限公司</td>\n",
       "      <td>有机肥料</td>\n",
       "      <td>粒状</td>\n",
       "      <td>0.0167</td>\n",
       "      <td>0.0167</td>\n",
       "      <td>0.0167</td>\n",
       "      <td>无氯</td>\n",
       "      <td>0.45</td>\n",
       "      <td>鄂农肥（2016）准字2048号</td>\n",
       "      <td>2016-10-10</td>\n",
       "      <td>2021-10-01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0501</td>\n",
       "      <td>6,1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>112</th>\n",
       "      <td>3670</td>\n",
       "      <td>3671</td>\n",
       "      <td>湖北吉顺磷化有限公司</td>\n",
       "      <td>有机肥料</td>\n",
       "      <td>粉状&amp;nbsp;&amp;nbsp;</td>\n",
       "      <td>0.0167</td>\n",
       "      <td>0.0167</td>\n",
       "      <td>0.0167</td>\n",
       "      <td>无氯</td>\n",
       "      <td>0.45</td>\n",
       "      <td>鄂农肥（2016）准字2083号</td>\n",
       "      <td>2016-10-20</td>\n",
       "      <td>2021-10-01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0501</td>\n",
       "      <td>6,1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>113</th>\n",
       "      <td>3671</td>\n",
       "      <td>3672</td>\n",
       "      <td>湖北吉顺磷化有限公司</td>\n",
       "      <td>有机肥料</td>\n",
       "      <td>粒状&amp;nbsp;&amp;nbsp;</td>\n",
       "      <td>0.0167</td>\n",
       "      <td>0.0167</td>\n",
       "      <td>0.0167</td>\n",
       "      <td>无氯</td>\n",
       "      <td>0.45</td>\n",
       "      <td>鄂农肥（2016）准字2084号</td>\n",
       "      <td>2016-10-20</td>\n",
       "      <td>2021-10-01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0501</td>\n",
       "      <td>6,1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     Unnamed: 0    序号               企业名称 产品通用名称            产品形态   总氮百分比  \\\n",
       "7           779   780  恒发新天地生物科技(大悟)有限公司   有机肥料              粉状  0.0200   \n",
       "110        3655  3656       湖北三宁化工股份有限公司   有机肥料              粒状  0.0167   \n",
       "111        3656  3657     荆州市丰禾源生物科技有限公司   有机肥料              粒状  0.0167   \n",
       "112        3670  3671         湖北吉顺磷化有限公司   有机肥料  粉状&nbsp;&nbsp;  0.0167   \n",
       "113        3671  3672         湖北吉顺磷化有限公司   有机肥料  粒状&nbsp;&nbsp;  0.0167   \n",
       "\n",
       "     P2O5百分比  K2O百分比 含氯情况  有机质百分比            正式登记证号        发证日期        有效期  \\\n",
       "7     0.0200  0.0200   无氯    0.45  鄂农肥（2011）准字0873号  2016-11-18 2021-11-01   \n",
       "110   0.0167  0.0167   无氯    0.45  鄂农肥（2016）准字2047号  2016-10-10 2021-10-01   \n",
       "111   0.0167  0.0167   无氯    0.45  鄂农肥（2016）准字2048号  2016-10-10 2021-10-01   \n",
       "112   0.0167  0.0167   无氯    0.45  鄂农肥（2016）准字2083号  2016-10-20 2021-10-01   \n",
       "113   0.0167  0.0167   无氯    0.45  鄂农肥（2016）准字2084号  2016-10-20 2021-10-01   \n",
       "\n",
       "    产品商品名称  适用作物  总无机养分百分比 label  \n",
       "7      NaN   NaN    0.0600   6,1  \n",
       "110    NaN   NaN    0.0501   6,1  \n",
       "111    NaN   NaN    0.0501   6,1  \n",
       "112    NaN   NaN    0.0501   6,1  \n",
       "113    NaN   NaN    0.0501   6,1  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 读取数据\n",
    "data = pd.read_excel('./result/result2_2.xlsx')\n",
    "# 取出2021年9月30日仍然有效的数据的数据\n",
    "\n",
    "\n",
    "# 将有效期改为时间类型\n",
    "data['有效期'] = pd.to_datetime(data['有效期'])\n",
    "\n",
    "# 将'2021/09/30'转换为时间类型\n",
    "t1 = pd.Timestamp('2021/09/30')\n",
    "\n",
    "# 提取出有效期大于2021/09/30的数据\n",
    "data = data[data['有效期']>t1]\n",
    "\n",
    "# 保存数据\n",
    "data.to_excel('./result/result3_2.xlsx',encoding='gbk')\n",
    "data.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "广西的简称为“桂”，湖北的简称为“鄂”\n",
    "从有效产品中分别筛选出广西和湖北"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<strong>广西产品登记数量在前 5 的组别</strong>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>数量</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>label</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>6,1</th>\n",
       "      <td>400</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7,1</th>\n",
       "      <td>79</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8,1</th>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9,1</th>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10,1</th>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        数量\n",
       "label     \n",
       "6,1    400\n",
       "7,1     79\n",
       "8,1     19\n",
       "9,1      6\n",
       "10,1     2"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 筛选出广西的数据\n",
    "data_guangxi = data.loc[data['正式登记证号'].str[0:1]=='桂']\n",
    "data_guangxi.groupby('label').count()\n",
    "data_guangxi = data_guangxi.groupby('label').count()\n",
    "data_guangxi = data_guangxi[['Unnamed: 0']]\n",
    "\n",
    "# 修改列名\n",
    "data_guangxi.rename(columns={'Unnamed: 0':'数量'},inplace=True) \n",
    "data_guangxi = data_guangxi.sort_values(by='数量', ascending=False)\n",
    "\n",
    "# 从大到小进行排序\n",
    "data_guangxi"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![](https://i.bmp.ovh/imgs/2021/11/2fe706e34819c5ba.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<strong>湖北产品登记数量在前 5 的组别</strong>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>数量</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>label</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>6,1</th>\n",
       "      <td>388</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7,1</th>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8,1</th>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10,1</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9,1</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        数量\n",
       "label     \n",
       "6,1    388\n",
       "7,1     13\n",
       "8,1      6\n",
       "10,1     1\n",
       "9,1      1"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 筛选出湖北的数据\n",
    "data_hubei = data.loc[data['正式登记证号'].str[0:1]=='鄂']\n",
    "group_hubei = data_hubei.groupby('label').count()\n",
    "group_hubei = group_hubei[['Unnamed: 0']]\n",
    "\n",
    "# 修改列名\n",
    "group_hubei.rename(columns={'Unnamed: 0':'数量'},inplace=True) \n",
    "group_hubei = group_hubei.sort_values(by='数量', ascending=False)\n",
    "\n",
    "# 从大到小进行排序\n",
    "group_hubei"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![](https://i.bmp.ovh/imgs/2021/11/ac5681890a9ff275.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![](https://i.bmp.ovh/imgs/2021/11/48a773df414a9d45.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "# 任务 3.3 从附件 3 中提取产品登记数量大于 10 的肥料企业，给出这些企业所用到的原料集合（发酵菌剂除外）。以各企业用到的原料作为特征，计算企业之间的杰卡德相似系数矩阵，并将结果（保留4位小数）保存到文件“result3_3.xlsx”\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'pd' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001B[1;31m---------------------------------------------------------------------------\u001B[0m",
      "\u001B[1;31mNameError\u001B[0m                                 Traceback (most recent call last)",
      "\u001B[1;32m<ipython-input-2-119a9612d00f>\u001B[0m in \u001B[0;36m<module>\u001B[1;34m()\u001B[0m\n\u001B[0;32m      1\u001B[0m \u001B[1;31m# 读取数据\u001B[0m\u001B[1;33m\u001B[0m\u001B[1;33m\u001B[0m\u001B[0m\n\u001B[1;32m----> 2\u001B[1;33m \u001B[0mdata\u001B[0m \u001B[1;33m=\u001B[0m \u001B[0mpd\u001B[0m\u001B[1;33m.\u001B[0m\u001B[0mread_excel\u001B[0m\u001B[1;33m(\u001B[0m\u001B[1;34m'./data/附件3.xlsx'\u001B[0m\u001B[1;33m)\u001B[0m\u001B[1;33m\u001B[0m\u001B[0m\n\u001B[0m",
      "\u001B[1;31mNameError\u001B[0m: name 'pd' is not defined"
     ]
    }
   ],
   "source": [
    "# 读取数据\n",
    "data = pd.read_excel('./data/附件3.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'data' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001B[1;31m---------------------------------------------------------------------------\u001B[0m",
      "\u001B[1;31mNameError\u001B[0m                                 Traceback (most recent call last)",
      "\u001B[1;32m<ipython-input-3-f799ea13f892>\u001B[0m in \u001B[0;36m<module>\u001B[1;34m()\u001B[0m\n\u001B[0;32m      1\u001B[0m \u001B[1;31m# 根据企业名称进行分组，得到每个企业登记产品的数量\u001B[0m\u001B[1;33m\u001B[0m\u001B[1;33m\u001B[0m\u001B[0m\n\u001B[0;32m      2\u001B[0m \u001B[1;31m# 除去发酵菌剂\u001B[0m\u001B[1;33m\u001B[0m\u001B[1;33m\u001B[0m\u001B[0m\n\u001B[1;32m----> 3\u001B[1;33m \u001B[0mgroup\u001B[0m \u001B[1;33m=\u001B[0m \u001B[0mdata\u001B[0m\u001B[1;33m.\u001B[0m\u001B[0mgroupby\u001B[0m\u001B[1;33m(\u001B[0m\u001B[1;34m'企业名称'\u001B[0m\u001B[1;33m)\u001B[0m\u001B[1;33m.\u001B[0m\u001B[0mcount\u001B[0m\u001B[1;33m(\u001B[0m\u001B[1;33m)\u001B[0m\u001B[1;33m\u001B[0m\u001B[0m\n\u001B[0m\u001B[0;32m      4\u001B[0m \u001B[0mgroup\u001B[0m \u001B[1;33m=\u001B[0m \u001B[0mgroup\u001B[0m\u001B[1;33m.\u001B[0m\u001B[0mdrop\u001B[0m\u001B[1;33m(\u001B[0m\u001B[1;34m'发酵菌剂'\u001B[0m\u001B[1;33m,\u001B[0m \u001B[0maxis\u001B[0m\u001B[1;33m=\u001B[0m\u001B[1;36m1\u001B[0m\u001B[1;33m)\u001B[0m\u001B[1;33m\u001B[0m\u001B[0m\n\u001B[0;32m      5\u001B[0m \u001B[1;33m\u001B[0m\u001B[0m\n",
      "\u001B[1;31mNameError\u001B[0m: name 'data' is not defined"
     ]
    }
   ],
   "source": [
    "# 根据企业名称进行分组，得到每个企业登记产品的数量\n",
    "# 除去发酵菌剂\n",
    "group = data.groupby('企业名称').count()\n",
    "group = group.drop('发酵菌剂', axis=1)\n",
    "\n",
    "# 筛选出登记数量大于 10 的肥料企业\n",
    "group = group[group['序号']>10]\n",
    "all_columns = group.columns.to_list()[1:]\n",
    "type_list = []\n",
    "\n",
    "# 将企业使用过的原料放入集合\n",
    "for x in all_columns:\n",
    "    if x!='发酵菌剂':\n",
    "        if (group[x]==0).all()==False:\n",
    "            type_list.append(x)\n",
    "        \n",
    "# 展示集合\n",
    "type_list"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "<strong>构建相似度矩阵（初始值为0）</strong>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'group' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001B[1;31m---------------------------------------------------------------------------\u001B[0m",
      "\u001B[1;31mNameError\u001B[0m                                 Traceback (most recent call last)",
      "\u001B[1;32m<ipython-input-1-dc882616c43e>\u001B[0m in \u001B[0;36m<module>\u001B[1;34m()\u001B[0m\n\u001B[1;32m----> 1\u001B[1;33m \u001B[0mgroup\u001B[0m\u001B[1;33m.\u001B[0m\u001B[0mindex\u001B[0m\u001B[1;33m\u001B[0m\u001B[0m\n\u001B[0m",
      "\u001B[1;31mNameError\u001B[0m: name 'group' is not defined"
     ]
    }
   ],
   "source": [
    "group.index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID1</th>\n",
       "      <th>ID10</th>\n",
       "      <th>ID12</th>\n",
       "      <th>ID2</th>\n",
       "      <th>ID3</th>\n",
       "      <th>ID4</th>\n",
       "      <th>ID5</th>\n",
       "      <th>ID6</th>\n",
       "      <th>ID7</th>\n",
       "      <th>ID9</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>ID1</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ID10</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ID12</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ID2</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ID3</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ID4</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ID5</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ID6</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ID7</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ID9</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      ID1  ID10  ID12  ID2  ID3  ID4  ID5  ID6  ID7  ID9\n",
       "ID1     0     0     0    0    0    0    0    0    0    0\n",
       "ID10    0     0     0    0    0    0    0    0    0    0\n",
       "ID12    0     0     0    0    0    0    0    0    0    0\n",
       "ID2     0     0     0    0    0    0    0    0    0    0\n",
       "ID3     0     0     0    0    0    0    0    0    0    0\n",
       "ID4     0     0     0    0    0    0    0    0    0    0\n",
       "ID5     0     0     0    0    0    0    0    0    0    0\n",
       "ID6     0     0     0    0    0    0    0    0    0    0\n",
       "ID7     0     0     0    0    0    0    0    0    0    0\n",
       "ID9     0     0     0    0    0    0    0    0    0    0"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 企业编号的列表\n",
    "company_list =  group.index.to_list()\n",
    "# 创建横坐标和纵坐标都为城市列表的矩阵,矩阵初始值为0\n",
    "df = pd.DataFrame(0, index=company_list, columns=['ID1', 'ID10', 'ID12', 'ID2', 'ID3', 'ID4', 'ID5', 'ID6', 'ID7', 'ID9'])\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<strong>计算每一个企业的相似度<strong>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# 每一个企业的原料使用情况\n",
    "group"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# 循环企业列表，根据使用原料情况来求出每一个企业的相似度\n",
    "for x in company_list:\n",
    "    for y in company_list:\n",
    "        if x!=y:\n",
    "            # 分别取出“企业x”和“企业y”的原料使用情况\n",
    "            A = group.loc[x]\n",
    "            B = group.loc[y]\n",
    "            # 交集\n",
    "            if 0 in list(set(group.loc[x]).intersection(set(group.loc[y]))):\n",
    "                intersections = len(list(set(group.loc[x]).intersection(set(group.loc[y]))))-1\n",
    "            else:\n",
    "                intersections = len(list(set(group.loc[x]).intersection(set(group.loc[y]))))\n",
    "            # 并集\n",
    "            if 0 in list(set(group.loc[x]).union(set(group.loc[y]))):\n",
    "                unions = len(list(set(group.loc[x]).union(set(group.loc[y]))))-1\n",
    "            else:\n",
    "                unions = len(list(set(group.loc[x]).union(set(group.loc[y]))))\n",
    "            # 计算相似度\n",
    "            same_rate = intersections/unions\n",
    "            # 将相似度放入矩阵\n",
    "            df.loc[x,y] = same_rate\n",
    "        else:\n",
    "            df.loc[x,y] = 1\n",
    "df.to_excel('./result/result3_3.xlsx')\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>序号</th>\n",
       "      <th>蘑菇渣</th>\n",
       "      <th>石灰渣</th>\n",
       "      <th>酒精发酵浓缩液</th>\n",
       "      <th>磷酸一铵</th>\n",
       "      <th>尿素</th>\n",
       "      <th>氧化钾</th>\n",
       "      <th>草木灰</th>\n",
       "      <th>氯化钾</th>\n",
       "      <th>甘蔗滤泥发酵熟料</th>\n",
       "      <th>...</th>\n",
       "      <th>牛骨粉</th>\n",
       "      <th>谷壳</th>\n",
       "      <th>生物菌剂</th>\n",
       "      <th>奶盖</th>\n",
       "      <th>酒糟粉</th>\n",
       "      <th>大颗粒磷酸二铵</th>\n",
       "      <th>大颗粒硫酸钾</th>\n",
       "      <th>大颗粒硫酸铵</th>\n",
       "      <th>种</th>\n",
       "      <th>55磷酸一铵</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>企业名称</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>ID1</th>\n",
       "      <td>40</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>29</td>\n",
       "      <td>28</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>22</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ID10</th>\n",
       "      <td>11</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>11</td>\n",
       "      <td>11</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>11</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ID12</th>\n",
       "      <td>11</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>7</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ID2</th>\n",
       "      <td>26</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>23</td>\n",
       "      <td>24</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>18</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ID3</th>\n",
       "      <td>19</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>19</td>\n",
       "      <td>19</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>15</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ID4</th>\n",
       "      <td>18</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>17</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>13</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ID5</th>\n",
       "      <td>18</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>10</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>7</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ID6</th>\n",
       "      <td>14</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>12</td>\n",
       "      <td>10</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>11</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ID7</th>\n",
       "      <td>13</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>13</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>10</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ID9</th>\n",
       "      <td>11</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>9</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>11</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>10 rows × 294 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      序号  蘑菇渣  石灰渣  酒精发酵浓缩液  磷酸一铵  尿素  氧化钾  草木灰  氯化钾  甘蔗滤泥发酵熟料  ...  牛骨粉  谷壳  \\\n",
       "企业名称                                                            ...            \n",
       "ID1   40    0    0        0    29  28    0    0   22         0  ...    0   0   \n",
       "ID10  11    0    0        0    11  11    0    0   11         0  ...    0   0   \n",
       "ID12  11    0    0        0     2   4    0    0    7         0  ...    0   0   \n",
       "ID2   26    0    0        0    23  24    0    0   18         0  ...    0   0   \n",
       "ID3   19    0    0        0    19  19    0    0   15         0  ...    0   0   \n",
       "ID4   18    0    0        0     0  17    0    0   13         0  ...    0   0   \n",
       "ID5   18    0    0        0    10   6    0    0    7         0  ...    0   0   \n",
       "ID6   14    0    0        0    12  10    0    0   11         0  ...    0   0   \n",
       "ID7   13    0    0        0    13   6    0    0   10         0  ...    0   0   \n",
       "ID9   11    0    0        0     9   0    0    0   11         0  ...    0   0   \n",
       "\n",
       "      生物菌剂  奶盖  酒糟粉  大颗粒磷酸二铵  大颗粒硫酸钾  大颗粒硫酸铵  种  55磷酸一铵  \n",
       "企业名称                                                     \n",
       "ID1      0   0    0        0       0       0  0       0  \n",
       "ID10     0   0    0        0       0       0  0       0  \n",
       "ID12     0   0    0        0       0       0  0       0  \n",
       "ID2      0   0    0        0       0       0  0       0  \n",
       "ID3      0   0    0        0       0       0  0       0  \n",
       "ID4      0   0    0        0       0       0  0       0  \n",
       "ID5      0   0    0        0       0       0  0       0  \n",
       "ID6      0   0    0        0       0       0  0       0  \n",
       "ID7      0   0    0        0       0       0  0       0  \n",
       "ID9      0   0    0        0       0       0  0       0  \n",
       "\n",
       "[10 rows x 294 columns]"
      ]
     },
     "execution_count": 66,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 每一个企业的原料使用情况\n",
    "group"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID1</th>\n",
       "      <th>ID10</th>\n",
       "      <th>ID12</th>\n",
       "      <th>ID2</th>\n",
       "      <th>ID3</th>\n",
       "      <th>ID4</th>\n",
       "      <th>ID5</th>\n",
       "      <th>ID6</th>\n",
       "      <th>ID7</th>\n",
       "      <th>ID9</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>ID1</th>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.200000</td>\n",
       "      <td>0.125000</td>\n",
       "      <td>0.166667</td>\n",
       "      <td>0.125000</td>\n",
       "      <td>0.222222</td>\n",
       "      <td>0.111111</td>\n",
       "      <td>0.285714</td>\n",
       "      <td>0.058824</td>\n",
       "      <td>0.125000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ID10</th>\n",
       "      <td>0.200000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.400000</td>\n",
       "      <td>0.214286</td>\n",
       "      <td>0.272727</td>\n",
       "      <td>0.285714</td>\n",
       "      <td>0.333333</td>\n",
       "      <td>0.555556</td>\n",
       "      <td>0.400000</td>\n",
       "      <td>0.555556</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ID12</th>\n",
       "      <td>0.125000</td>\n",
       "      <td>0.400000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.307692</td>\n",
       "      <td>0.400000</td>\n",
       "      <td>0.285714</td>\n",
       "      <td>0.600000</td>\n",
       "      <td>0.400000</td>\n",
       "      <td>0.400000</td>\n",
       "      <td>0.272727</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ID2</th>\n",
       "      <td>0.166667</td>\n",
       "      <td>0.214286</td>\n",
       "      <td>0.307692</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.307692</td>\n",
       "      <td>0.312500</td>\n",
       "      <td>0.357143</td>\n",
       "      <td>0.214286</td>\n",
       "      <td>0.214286</td>\n",
       "      <td>0.062500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ID3</th>\n",
       "      <td>0.125000</td>\n",
       "      <td>0.272727</td>\n",
       "      <td>0.400000</td>\n",
       "      <td>0.307692</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.500000</td>\n",
       "      <td>0.333333</td>\n",
       "      <td>0.272727</td>\n",
       "      <td>0.400000</td>\n",
       "      <td>0.166667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ID4</th>\n",
       "      <td>0.222222</td>\n",
       "      <td>0.285714</td>\n",
       "      <td>0.285714</td>\n",
       "      <td>0.312500</td>\n",
       "      <td>0.500000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.428571</td>\n",
       "      <td>0.285714</td>\n",
       "      <td>0.285714</td>\n",
       "      <td>0.285714</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ID5</th>\n",
       "      <td>0.111111</td>\n",
       "      <td>0.333333</td>\n",
       "      <td>0.600000</td>\n",
       "      <td>0.357143</td>\n",
       "      <td>0.333333</td>\n",
       "      <td>0.428571</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.333333</td>\n",
       "      <td>0.454545</td>\n",
       "      <td>0.333333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ID6</th>\n",
       "      <td>0.285714</td>\n",
       "      <td>0.555556</td>\n",
       "      <td>0.400000</td>\n",
       "      <td>0.214286</td>\n",
       "      <td>0.272727</td>\n",
       "      <td>0.285714</td>\n",
       "      <td>0.333333</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.272727</td>\n",
       "      <td>0.272727</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ID7</th>\n",
       "      <td>0.058824</td>\n",
       "      <td>0.400000</td>\n",
       "      <td>0.400000</td>\n",
       "      <td>0.214286</td>\n",
       "      <td>0.400000</td>\n",
       "      <td>0.285714</td>\n",
       "      <td>0.454545</td>\n",
       "      <td>0.272727</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.272727</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ID9</th>\n",
       "      <td>0.125000</td>\n",
       "      <td>0.555556</td>\n",
       "      <td>0.272727</td>\n",
       "      <td>0.062500</td>\n",
       "      <td>0.166667</td>\n",
       "      <td>0.285714</td>\n",
       "      <td>0.333333</td>\n",
       "      <td>0.272727</td>\n",
       "      <td>0.272727</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           ID1      ID10      ID12       ID2       ID3       ID4       ID5  \\\n",
       "ID1   1.000000  0.200000  0.125000  0.166667  0.125000  0.222222  0.111111   \n",
       "ID10  0.200000  1.000000  0.400000  0.214286  0.272727  0.285714  0.333333   \n",
       "ID12  0.125000  0.400000  1.000000  0.307692  0.400000  0.285714  0.600000   \n",
       "ID2   0.166667  0.214286  0.307692  1.000000  0.307692  0.312500  0.357143   \n",
       "ID3   0.125000  0.272727  0.400000  0.307692  1.000000  0.500000  0.333333   \n",
       "ID4   0.222222  0.285714  0.285714  0.312500  0.500000  1.000000  0.428571   \n",
       "ID5   0.111111  0.333333  0.600000  0.357143  0.333333  0.428571  1.000000   \n",
       "ID6   0.285714  0.555556  0.400000  0.214286  0.272727  0.285714  0.333333   \n",
       "ID7   0.058824  0.400000  0.400000  0.214286  0.400000  0.285714  0.454545   \n",
       "ID9   0.125000  0.555556  0.272727  0.062500  0.166667  0.285714  0.333333   \n",
       "\n",
       "           ID6       ID7       ID9  \n",
       "ID1   0.285714  0.058824  0.125000  \n",
       "ID10  0.555556  0.400000  0.555556  \n",
       "ID12  0.400000  0.400000  0.272727  \n",
       "ID2   0.214286  0.214286  0.062500  \n",
       "ID3   0.272727  0.400000  0.166667  \n",
       "ID4   0.285714  0.285714  0.285714  \n",
       "ID5   0.333333  0.454545  0.333333  \n",
       "ID6   1.000000  0.272727  0.272727  \n",
       "ID7   0.272727  1.000000  0.272727  \n",
       "ID9   0.272727  0.272727  1.000000  "
      ]
     },
     "execution_count": 86,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 循环企业列表，根据使用原料情况来求出每一个企业的相似度\n",
    "for x in company_list:\n",
    "    for y in company_list:\n",
    "        if x!=y:\n",
    "            # 分别取出“企业x”和“企业y”的原料使用情况\n",
    "            A = group.loc[x]\n",
    "            B = group.loc[y]\n",
    "            # 交集\n",
    "            if 0 in list(set(group.loc[x]).intersection(set(group.loc[y]))):\n",
    "                intersections = len(list(set(group.loc[x]).intersection(set(group.loc[y]))))-1\n",
    "            else:\n",
    "                intersections = len(list(set(group.loc[x]).intersection(set(group.loc[y]))))\n",
    "            # 并集\n",
    "            if 0 in list(set(group.loc[x]).union(set(group.loc[y]))):\n",
    "                unions = len(list(set(group.loc[x]).union(set(group.loc[y]))))-1\n",
    "            else:\n",
    "                unions = len(list(set(group.loc[x]).union(set(group.loc[y]))))\n",
    "            # 计算相似度\n",
    "            same_rate = intersections/unions\n",
    "            # 将相似度放入矩阵\n",
    "            df.loc[x,y] = format(same_rate,'.4f')\n",
    "        else:\n",
    "            df.loc[x,y] = 1\n",
    "df.to_excel('./result/result3_3.xlsx')\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.5"
  },
  "pycharm": {
   "stem_cell": {
    "cell_type": "raw",
    "metadata": {
     "collapsed": false
    },
    "source": []
   }
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}